#!/usr/bin/env python
# coding=utf-8
import os
import sys

# 当前文件的路径

pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util


def yesterday_shield():
    """昨日屏蔽data form"""
    df = pd.read_sql_query(yesterday_shield_sql(), db_util.con_youjia)
    return df


def shield_yesterday():
    """屏蔽昨日data form"""
    df = pd.read_sql_query(shield_yesterday_sql(), db_util.con_youjia)
    return df


def shield_today():
    """屏蔽今日"""
    df = pd.read_sql_query(shield_torday_sql(), db_util.con_youjia)
    return df


def yesterday_shield_sql():
    """昨日屏蔽sql"""
    sql = """
    SELECT
      CONCAT(IFNULL(lhs.modified_time, lhs.create_time), '') 操作时间,
      CASE whs.own_type
        WHEN 1 THEN '有家'
        WHEN 2 THEN '城宿'
        WHEN 3 THEN '斯维登'
        ELSE ''
      END 房源归属方,
      whs.city_name 城市,
      whs.work_name 门店,
      CONCAT(whs.house_id, '') 房屋ID,
      lh.title_sub 房屋昵称,
      group_concat(DISTINCT u1.realname ORDER BY uhr1.master DESC) 线上管家,
      lhs.remark 关房原因,
      CONCAT(lhs.dt, '') 关房日期,
      IFNULL(um.realname, uc.realname) 操作人
    FROM youjia.lod_house_stock lhs
      LEFT JOIN youjia_report.wh_house_static whs on lhs.house_id = whs.house_id
      LEFT JOIN youjia.lod_house lh ON lh.id = lhs.house_id
      LEFT JOIN youjia.users um ON um.id = lhs.modifier_id
      LEFT JOIN youjia.users uc ON uc.id = lhs.creator_id
      LEFT JOIN youjia.lodge l ON l.id = lh.lodge_id
      LEFT JOIN youjia.user_house_relation uhr1 ON uhr1.house_id = whs.house_id AND uhr1.user_role_id = 5 AND uhr1.is_delete = 0
      LEFT JOIN youjia.users u1 ON u1.id = uhr1.user_id
    WHERE lhs.state = 0 AND whs.house_state=100 AND date_format(IFNULL(lhs.modified_time, lhs.create_time), '%Y-%m-%d') = date_add(date_format(curdate(),'%Y-%m-%d'), interval -1 day)
    GROUP BY lh.id, lhs.dt
    ORDER BY whs.city_id;
    """
    return sql


def shield_yesterday_sql():
    """屏蔽昨日sql"""
    sql = """
    SELECT
      CONCAT(IFNULL(lhs.modified_time, lhs.create_time), '') 操作时间,
      CASE whs.own_type
        WHEN 1 THEN '有家'
        WHEN 2 THEN '城宿'
        WHEN 3 THEN '斯维登'
      ELSE ''
      END 房源归属方,
      whs.city_name 城市,
      whs.work_name 门店,
      CONCAT(whs.house_id, '') 房屋ID,
      lh.title_sub 房屋昵称,
      GROUP_CONCAT(DISTINCT u1.realname ORDER BY uhr1.master DESC) 线上管家,
      lhs.remark 关房原因,
      CONCAT(lhs.dt, '') 关房日期,
      IFNULL(um.realname, uc.realname) 操作人,
      CASE llj.type
        WHEN 0 THEN '果加'
        WHEN 1 THEN '丁丁'
        WHEN 2 THEN '东胜'
        WHEN 3 THEN '海草'
      ELSE '其他'
      END 门锁品牌,
      group_concat(
          concat(
              '/',
              if(llor.user_mobile='' or llor.user_mobile is null,'无',llor.user_mobile), ';',
              CASE llor.op_way
                WHEN 0 THEN 'APP 蓝牙开门'
                WHEN 1 THEN '自定义密码开门'
                WHEN 2 THEN '一次性密码开门'
                WHEN 3 THEN '远程开门'
                WHEN 4 THEN '时效密码开门'
                WHEN 5 THEN '门禁卡开门'
                WHEN 6 THEN '物理钥匙开门'
                ELSE '-'
                END, ';',
              llor.open_time
          )
      ) 开门记录
    FROM youjia.lod_house_stock lhs
      LEFT JOIN youjia_report.wh_house_static whs ON lhs.house_id = whs.house_id
      LEFT JOIN youjia.lod_house lh ON lh.id = lhs.house_id
      LEFT JOIN youjia.users um ON um.id = lhs.modifier_id
      LEFT JOIN youjia.users uc ON uc.id = lhs.creator_id
      LEFT JOIN youjia.lod_lock_join llj ON llj.lod_house_id = lhs.house_id AND llj.is_deleted = 0
      LEFT JOIN youjia.lodge l ON l.id = lh.lodge_id
      LEFT JOIN youjia.user_house_relation uhr1 ON uhr1.house_id = lh.id AND uhr1.user_role_id = 5 AND uhr1.is_delete = 0
      LEFT JOIN youjia.users u1 ON u1.id = uhr1.user_id
      LEFT JOIN youjia.lod_lock_open_record llor ON llor.lock_no = llj.lock_no AND is_delete = 0 AND date_format(llor.open_time, '%Y-%m-%d') = lhs.dt
    WHERE lhs.state = 0 AND whs.house_state=100 AND lhs.dt = date_add(curdate(), interval -1 day)
    GROUP BY lh.id, lhs.dt
    ORDER BY whs.city_id;
    """
    return sql


def shield_torday_sql():
    sql = """
    SELECT
      CONCAT(IFNULL(lhs.modified_time, lhs.create_time), '') 操作时间,
      CASE whs.own_type
        WHEN 1 THEN '有家'
        WHEN 2 THEN '城宿'
        WHEN 3 THEN '斯维登'
        ELSE ''
      END 房源归属方,
      whs.city_name 城市,
      whs.work_name 门店,
      CONCAT(whs.house_id, '') 房屋ID,
      lh.title_sub 房屋昵称,
      group_concat(DISTINCT u1.realname ORDER BY uhr1.master DESC) 线上管家,
      lhs.remark 关房原因,
      CONCAT(lhs.dt, '') 关房日期,
      IFNULL(um.realname, uc.realname) 操作人
    FROM youjia.lod_house_stock lhs
      LEFT JOIN youjia_report.wh_house_static whs on lhs.house_id = whs.house_id
      LEFT JOIN youjia.lod_house lh ON lh.id = lhs.house_id
      LEFT JOIN youjia.users um ON um.id = lhs.modifier_id
      LEFT JOIN youjia.users uc ON uc.id = lhs.creator_id
      LEFT JOIN youjia.lodge l ON l.id = lh.lodge_id
      LEFT JOIN youjia.user_house_relation uhr1 ON uhr1.house_id = whs.house_id AND uhr1.user_role_id = 5 AND uhr1.is_delete = 0
      LEFT JOIN youjia.users u1 ON u1.id = uhr1.user_id
    WHERE lhs.state = 0 AND whs.house_state=100 AND lhs.dt = curdate()
    GROUP BY lh.id, lhs.dt
    ORDER BY whs.city_id;
    """
    return sql
